<%@page trimDirectiveWhitespaces="true"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@page import="java.util.regex.Pattern"%>
<%@ page import = "java.sql.PreparedStatement" %>




<% 
	//get session
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
	String name = String.valueOf(session.getAttribute("name"));
	String surname = String.valueOf(session.getAttribute("surname"));

	//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	




		System.out.println("Active timetable_import_background !!");
		String file_pathx = request.getParameter("file_path");
		System.out.println("File_path background:"+file_pathx);
		
		
		
		String course_str ="";
		String course_id="";
		String year = "";
		String semester = "";
		String monday = "";
		String tuesday= "";
		String wednesday="";
		String thursday="";
		String friday="";
		String saturday = "";
		String sunday="";
		String lec_name = "";
		String lec_surname = "";
		String lec_id ="";
		String room = "";
		
		
		
		
		
		
        int i = 0;
        int num_row = 0;
        int num_col  = 0;
		int class_added=0;
		
		List<String> fail_list = new ArrayList<String>();
		List<String> error_log = new ArrayList<String>();
		
		java.util.Date dt = new java.util.Date();

		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

		String date_now = sdf.format(dt);
		
		int count = 0;
		

		String[] header_array = new  String[]{"รหัสวิชา","ปีการศึกษา","เทอม","ห้องเรียน",
				  "จันทร์","อังคาร","พุธ","พฤหัส",
				  "ศุกร์","เสาร์","อาทิตย์","ชื่อ(อาจารย์)","นามสกุล(อาจารย์)"};
			
			System.out.println("Header_array size : "+ header_array.length);

		
		try{
			
			FileInputStream file = new FileInputStream(new File(file_pathx));
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			 
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            
            Iterator<Row> headerIt_row_check = sheet.iterator();
            Row row_check = headerIt_row_check.next();
            Iterator<Cell> headerIt_cell_check = row_check.cellIterator();

            while (rowIterator.hasNext())
            {	
            	System.out.println(" //////New Row/////  ");
            	System.out.println("Row:"+num_row);
            	if(count==0)
            	{
            		while(headerIt_cell_check.hasNext()){
            			Cell header_cell = headerIt_cell_check.next();
            			   switch (header_cell.getCellType())
                           {
                               case Cell.CELL_TYPE_NUMERIC:
                                   break;
                               case Cell.CELL_TYPE_STRING:
                                  // System.out.print(header_cell.getStringCellValue()+ " ");
                                  	 System.out.println(header_array[i]+":"+header_cell.getStringCellValue());
                                   	 if(header_array[i].equals(header_cell.getStringCellValue()))
                                   	 {
                                   		 System.out.println("Pass:" + i);
                                   	 }
                                   	 else{
                                   		 
                                   		throw new Exception();
                                   		
                                   	 }
                                   	 
                                   break;
                           }// end switch
            				i++;
            		}// end headerIt_cell_check.hasNext()
            		System.out.println("");
            		count = count +1;
            		rowIterator.next();
            		System.out.println("////////// END Header////////////");
            	}// end if(count==0)
            	
            	  
            	try{	
            			
		            	Row row = rowIterator.next();
						
		                	// rowIterator.next();
		             	
		                	//For each row, iterate through all the columns
		             	   Iterator<Cell> cellIterator = row.cellIterator();
		             	
		                while (cellIterator.hasNext())
		                {
		                	
		                    Cell cell = cellIterator.next();
		                    //Check the cell type and format accordingly
		                    System.out.println("num_col:"+num_col);
		                    
		                	switch(num_col){
	                   		case 0 :
	                   			
				      					course_str = cell.getStringCellValue();
				      					System.out.println("course_str:"+course_str);    
				      					
								break;
	                   		case 1 :
	                   			
	                   			try {
	                   				
	                   				int temp_year = Integer.parseInt(cell.getStringCellValue().replaceAll("\\s+",""));
	                				temp_year = temp_year - 543 ; 
                   					year = String.valueOf(temp_year);	 
	                   				
	                 			
	                 			}catch(Exception x){
	                   			
	                   				year = String.valueOf(cell.getNumericCellValue()-543);
	                   				String [] year_temp = year.split(Pattern.quote("."));
	                   				
	                   				year = year_temp[0];
	                   			
	                 			}
	                   				
	                   					
	                   					System.out.println("year:"+year);
	                   			break;
	                   		case 2 :
	            
	                   				semester = String.valueOf(cell.getStringCellValue());
	                   				System.out.println("semester:"+semester);
	                   			break;
	                   		case 3 :
	                   			
	                   				room  = cell.getStringCellValue();
	                   				System.out.println("classroom:"+room);
	                   			break;
	                   		case 4 :
	                   			
	                   				monday = cell.getStringCellValue();
	                   				System.out.println("monday:"+monday);
	                   			break;
	                   		case 5 :
	                   				
	                   				tuesday = cell.getStringCellValue();
	                   				System.out.println("tuesday:"+tuesday);
	                   			break;
	                   		case 6 :
	                   			 
	            					wednesday = cell.getStringCellValue();
                   					System.out.println("wednesday:"+wednesday);
	                          	 
	                   			break;
	                   		case 7 :
	                				thursday = cell.getStringCellValue();
               						System.out.println("thursday:"+thursday);
	                   			break;
	                   		case 8 :
	                   				friday = cell.getStringCellValue();
	                   				System.out.println("friday:"+friday);
	                   			break;
	                   		case 9:
	                   				saturday = cell.getStringCellValue();
	                   				System.out.println("saturday:"+saturday);
	                   		      				
	                 			break;
	                   		case 10:
	                   		
		                   			sunday = cell.getStringCellValue();
	    	           				System.out.println("sunday:"+sunday);           			
	                   			
	                   			break;
	                   	
	                   		case 11 :
	                   		
	                   				lec_name = cell.getStringCellValue();
	                   				System.out.println("lec_name:"+lec_name);
	                   			break;
	                   		case 12 :
	                   				lec_surname = cell.getStringCellValue();
	                   				System.out.println("lec_surname:"+lec_surname);
	                   			break;
	
	                   	}
		                    
		                    num_col++;
		                }// end cellIterator.hasNext();
		                num_col=0;
		                num_row++;
		                
		                //Start import process
		            	//Phase get course_id 
		            	System.out.println("Phase 1 Get Course_id");
		                System.out.println("course_str:"+course_str);
		                String sql_course = " SELECT * "
								+ " FROM course_name "
								+ " WHERE course_id='" +course_str +"'";
		            	
		            	ResultSet rs_course =	connect.createStatement().executeQuery(" SELECT * "
																					+ " FROM course_name "
																					+ " WHERE course_id='" +course_str +"'");
													
		            	
		            	rs_course.last();
		                if(rs_course.getRow()>=1)
		                {
		                	// existing course
		                	System.out.println("Phase 2 get lecturer index");
		                	System.out.println("lec_name:"+lec_name+":");
		                	System.out.println("lec_surname:"+lec_surname+":");
		            		String sql_lec = " SELECT L.index "+
									   " FROM lecturer L, full_name F"+
									   " WHERE L.index=F.index "+
									   " AND F.name_th='"+lec_name+"'" +
									   " AND F.surname_th='"+lec_surname+"'";

		                	ResultSet rs_lec =	connect.createStatement().executeQuery(" SELECT L.index "+
		                															   " FROM lecturer L, full_name F"+
		                															   " WHERE L.index=F.index "+
		                															   " AND F.name_th='"+lec_name+"'" +
		                															   " AND F.surname_th='"+lec_surname+"'");
		                	
		                	rs_lec.last();
		                	if(rs_lec.getRow()>=1)
		                	{
		                		//existing lecturer
		                		System.out.println("found Lecturer");
		                		lec_id = rs_lec.getString("index");
		                		
		                		System.out.println("Phase3 add class");
		                		System.out.println("course_str:"+course_str);
		                		
		                		String sql = " INSERT INTO `class`(`course_id`, `index`, `year`, `semester`, `room`, `monday`,"+
										"`tuesday`, `wednesday`, `thursday`, `friday`, `saturday`, `sunday`)"+
										" VALUES ('"+course_str+"', '"+lec_id+"','"+year+"','"+semester+"','"+room+"',"+
									 	"'"+monday+"','"+tuesday+"','"+wednesday+"','"+thursday+"','"+friday+"',"+
									 	"'"+saturday+"','"+sunday+"')";
		                		
		                		System.out.println("sql_statement:"+sql);
		                		
		                				
		                		connect.createStatement().executeUpdate(sql);
		                		
		                		/* Log file */
		            			String log = strUser+" "+name+" "+surname+" add class";
		            			Log.writeUserFile(log);
		            			Log.writeUserFile(sql);	
		                											 	
		                		System.out.println("Add Class success");
		                		class_added++;
		                		
		                		
		                	}else{
		                		System.out.println("not found lecturer");
		                		fail_list.add(num_row+1+"");
		                		error_log.add(""+num_row+": not found lecturer:"+sql_lec);
		                		// not existing lecturer
		                		
		                	}
		                	
		                	
		                	
		                	
		                	
		                	
		                }else{
		                	// not existing course
		                	System.out.println("not found course");
		                	error_log.add(""+num_row+": not found course:"+sql_course);
		                	fail_list.add(num_row+1+"");
		                	
		                }
								
	
										
		                   			
		                   			
		                   			
		                   			
		                   			
		                   			
		                   			
            	} catch(Exception getData){ //end try Row row = rowIterator.next();
            		System.out.println("Error cant't read Excel:"+getData);
            
            		num_col = 0;
            		fail_list.add(num_row+1+"");
            		num_row++;
            	}
              
           
               	
            }// end while while(rowIterator.hasNext())
            file.close();
            System.out.println("fail_list size :"+fail_list.size());
            for(int num_fail= 0;num_fail<fail_list.size();num_fail++)
            {
            	System.out.println("Fail to import row:"+fail_list.get(num_fail).toString());
            }
            for(int temp_num = 0;temp_num<error_log.size();temp_num++)
            {
            	System.out.println(error_log.get(temp_num).toString());
            }
       
           
            String fail_list_all ="";
            if(fail_list.size()==0)
            {
            	fail_list_all ="";
            }
            else{
            	  fail_list_all = "ข้อมูลผิดพลาดในแถวที่ :";
            	  for(int x=0 ;x<fail_list.size();x++)
                  {
            		 
                  	fail_list_all = fail_list_all+fail_list.get(x).toString()+" , ";
                  }
            	  fail_list_all = fail_list_all + " \n"+ "กรุณาตรวจสอบข้อมูลดังกล่าว ก่อนนำเข้าอีกครั้ง";
            }

           
			out.print("success: to Add : "+class_added+" to class table.  \n"+fail_list_all);
			
			
		}//end try fileinputstream
		catch(Exception x){
			System.out.println(x);
			out.print("fail");
			
		}

		
		connect.close();

%>